<?php
App::uses('AppModel', 'Model');
/**
 * Tarif Model
 *
 * @property Reponse $Reponse
 * @property Article $Article
 */
class Tarif extends AppModel {
	
	function peterLeTarif($id) {
		$this->query("DELETE FROM tarifs WHERE id_tarif = ".$id);
	}
	
	function reglesTarifs() {
		return $this->query(
				"select * from (".
				"SELECT 0 candel, 100 sort, tarifs.id_tarif, ".
				" articles.description_article, ".
				" qi.libelle_question l_q_1, ".
				" ri.libelle_reponse_combo, ".
				" concat(tarif_calcules.coefficient_tarif, concat(' * ', qm.libelle_question)) coin ".
				" FROM tarifs ".
				" JOIN reponses ri ON tarifs.id_reponse = ri.id_reponse ".
				" JOIN questions qi ON ri.id_question = qi.id_question ".
				"JOIN articles ON articles.id_article = tarifs.id_article ".
				"JOIN tarif_calcules ON tarif_calcules.id_tarif = tarifs.id_tarif ".
				"JOIN questions qm ON qm.id_question = tarif_calcules.id_question  ". // question magique
				"UNION  ".
				"SELECT 1 candel, 50 sort, tarifs.id_tarif, ".
				" articles.description_article	, ".
				" qi.libelle_question, ".
				" ri.libelle_reponse_combo, tarifs.tarif_article_brut ".
				" FROM tarifs ".
				" JOIN reponses ri ON tarifs.id_reponse = ri.id_reponse ".
				" JOIN questions qi ON ri.id_question = qi.id_question ".
				"JOIN articles ON articles.id_article = tarifs.id_article ".
				") nos_tarifs ".
				" order by id_tarif, sort"
				);
	}
	
	
	// retourn les r�gles de type devis sous le format suivant :
	// un tableau contenant des tableau associatif, correspondant chacun � une r�gle. Chaque tableau associatif est de la forme suivante :
	// - id_article : l'id de l'article qui voit son tarif modifi�
	// - id_reponse : l'id de la r�ponse qui conditionne l'activation
	// - nouveau_tarif : le nouveau tarif appliqu�
	function regleDevis($id_questionnaire) {
		$resultat =
		$this->query("SELECT tarifs.id_reponse, tarifs.id_article, tarifs.tarif_article_brut + sum( tarif_calcules.coefficient_tarif * coalesce( reponses_donnees.reponse_entier, reponses_donnees.reponse_quantite_article, reponses_donnees.reponse_donnee_oui_non ) ) AS nouveau_tarif FROM tarifs ".
					 "JOIN reponses ON reponses.id_reponse = tarifs.id_reponse ".
					 "JOIN tarif_calcules ON tarif_calcules.id_tarif = tarifs.id_tarif ".
					 "JOIN questions ON questions.id_question = tarif_calcules.id_question ".
					 "LEFT JOIN reponses_donnees ON reponses_donnees.id_question = questions.id_question ".
					 "WHERE id_questionnaire = $id_questionnaire ".
					 "GROUP BY tarifs.id_tarif ".
					 "UNION ".
					 "SELECT tarifs.id_reponse, tarifs.id_article, tarifs.tarif_article_brut AS nouveau_tarif ".
					 "FROM tarifs ".
					 "JOIN reponses ON reponses.id_reponse = tarifs.id_reponse ".
					 "LEFT JOIN tarif_calcules ON tarif_calcules.id_tarif = tarifs.id_tarif ".
					 "WHERE tarif_calcules.id_tarif IS NULL ".
					 "GROUP BY tarifs.id_tarif");
		
		$regle = array();
		
		foreach ($resultat as $ligne) {
			$regle[] = array('id_article' => $ligne[0]['id_article'], 'id_reponse' => $ligne[0]['id_reponse'], 'nouveau_tarif' => $ligne[0]['nouveau_tarif']);
		}
		
		return $regle;
	}
/**
 * Primary key field
 *
 * @var string
 */
	public $primaryKey = 'id_tarif';
/**
 * Display field
 *
 * @var string
 */
	public $displayField = 'tarif_article_brut';

	//The Associations below have been created with all possible keys, those that are not needed can be removed

/**
 * hasOne associations
 *
 * @var array
 */
	public $hasOne = array(
		'Reponse' => array(
			'className' => 'Reponse',
			'foreignKey' => 'id_reponse',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		),
		'Article' => array(
			'className' => 'Article',
			'foreignKey' => 'id_article',
			'conditions' => '',
			'fields' => '',
			'order' => ''
		)
	);
}
